const { pool } = require('../config/database');

class FoodCategory {
  // 获取所有食品种类
  static async getAllCategories() {
    try {
      const [rows] = await pool.execute(`
        SELECT * FROM food_categories
        ORDER BY id ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取食品种类列表失败:', error.message);
      throw error;
    }
  }

  // 根据ID获取食品种类详情
  static async getCategoryById(id) {
    try {
      const [rows] = await pool.execute(`
        SELECT * FROM food_categories WHERE id = ?
      `, [id]);

      if (rows.length === 0) return null;
      return rows[0];
    } catch (error) {
      console.error('获取食品种类详情失败:', error.message);
      throw error;
    }
  }

  // 根据type_value获取食品种类详情
  static async getCategoryByValue(typeValue) {
    try {
      const [rows] = await pool.execute(`
        SELECT * FROM food_categories WHERE type_value = ?
      `, [typeValue]);

      if (rows.length === 0) return null;
      return rows[0];
    } catch (error) {
      console.error('根据值获取食品种类失败:', error.message);
      throw error;
    }
  }

  // 创建食品种类
  static async createCategory(categoryData) {
    try {
      const { type_name, type_value } = categoryData;
      
      const [result] = await pool.execute(`
        INSERT INTO food_categories (type_name, type_value)
        VALUES (?, ?)
      `, [type_name, type_value]);

      return {
        id: result.insertId,
        ...categoryData
      };
    } catch (error) {
      console.error('创建食品种类失败:', error.message);
      throw error;
    }
  }

  // 更新食品种类
  static async updateCategory(id, categoryData) {
    try {
      const { type_name, type_value } = categoryData;
      
      await pool.execute(`
        UPDATE food_categories 
        SET type_name = ?, type_value = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [type_name, type_value, id]);

      return await this.getCategoryById(id);
    } catch (error) {
      console.error('更新食品种类失败:', error.message);
      throw error;
    }
  }

  // 删除食品种类（级联删除相关的食物模板和食物）
  static async deleteCategory(id) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      // 1. 首先获取要删除的食品种类信息
      const [categoryRows] = await connection.execute(`
        SELECT type_name FROM food_categories WHERE id = ?
      `, [id]);

      if (categoryRows.length === 0) {
        await connection.rollback();
        return null;
      }

      const typeName = categoryRows[0].type_name;
      let deletedStats = {
        category: typeName,
        deletedFoods: 0,
        deletedTemplates: 0,
        templateIds: []
      };

      // 2. 获取所有相关的食物模板ID和统计信息
      const [templateRows] = await connection.execute(`
        SELECT id FROM food_templates WHERE food_type = ?
      `, [typeName]);

      // 3. 如果有相关的食物模板，统计并删除使用这些模板的所有食物
      if (templateRows.length > 0) {
        const templateIds = templateRows.map(template => template.id);
        deletedStats.templateIds = templateIds;
        const placeholders = templateIds.map(() => '?').join(',');
        
        // 先统计要删除的食物数量
        const [foodCountRows] = await connection.execute(`
          SELECT COUNT(*) as count FROM foods WHERE template_id IN (${placeholders})
        `, templateIds);
        deletedStats.deletedFoods = foodCountRows[0].count;
        
        // 删除食物
        await connection.execute(`
          DELETE FROM foods WHERE template_id IN (${placeholders})
        `, templateIds);
        
        console.log(`删除了 ${deletedStats.deletedFoods} 个食物记录`);
      }

      // 4. 删除相关的食物模板
      const [templateDeleteResult] = await connection.execute(`
        DELETE FROM food_templates WHERE food_type = ?
      `, [typeName]);
      
      deletedStats.deletedTemplates = templateDeleteResult.affectedRows;
      console.log(`删除了 ${deletedStats.deletedTemplates} 个食物模板`);

      // 5. 最后删除食品种类
      const [categoryDeleteResult] = await connection.execute(`
        DELETE FROM food_categories WHERE id = ?
      `, [id]);

      await connection.commit();
      
      console.log(`成功删除食品种类 "${typeName}" 及其相关数据:`, deletedStats);
      
      if (categoryDeleteResult.affectedRows > 0) {
        return deletedStats;
      } else {
        return null;
      }
    } catch (error) {
      await connection.rollback();
      console.error('删除食品种类失败:', error.message);
      throw error;
    } finally {
      connection.release();
    }
  }

  // 批量创建食品种类
  static async batchCreateCategories(categoriesList) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      const createdCategories = [];
      for (const categoryData of categoriesList) {
        const { type_name, type_value } = categoryData;

        const [result] = await connection.execute(`
          INSERT INTO food_categories (type_name, type_value)
          VALUES (?, ?)
        `, [type_name, type_value]);

        createdCategories.push({
          id: result.insertId,
          ...categoryData
        });
      }

      await connection.commit();
      return createdCategories;
    } catch (error) {
      await connection.rollback();
      console.error('批量创建食品种类失败:', error.message);
      throw error;
    } finally {
      connection.release();
    }
  }

  // 搜索食品种类
  static async searchCategories(keyword) {
    try {
      const [rows] = await pool.execute(`
        SELECT * FROM food_categories
        WHERE type_name LIKE ? OR type_value LIKE ?
        ORDER BY id ASC
      `, [`%${keyword}%`, `%${keyword}%`]);
      return rows;
    } catch (error) {
      console.error('搜索食品种类失败:', error.message);
      throw error;
    }
  }

  // 获取食品种类统计信息
  static async getCategoryStats() {
    try {
      const [totalCount] = await pool.execute(`
        SELECT COUNT(*) as total FROM food_categories
      `);

      return {
        total: totalCount[0].total
      };
    } catch (error) {
      console.error('获取食品种类统计失败:', error.message);
      throw error;
    }
  }

  // 检查type_value是否已存在
  static async checkValueExists(typeValue, excludeId = null) {
    try {
      let query = 'SELECT COUNT(*) as count FROM food_categories WHERE type_value = ?';
      let params = [typeValue];
      
      if (excludeId) {
        query += ' AND id != ?';
        params.push(excludeId);
      }

      const [rows] = await pool.execute(query, params);
      return rows[0].count > 0;
    } catch (error) {
      console.error('检查type_value是否存在失败:', error.message);
      throw error;
    }
  }

  // 获取格式化的种类选项（用于前端下拉选择）
  static async getCategoryOptions() {
    try {
      const [rows] = await pool.execute(`
        SELECT id, type_name as label, type_value as value FROM food_categories
        ORDER BY id ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取食品种类选项失败:', error.message);
      throw error;
    }
  }
}

module.exports = FoodCategory;
